package ch.prokopovi.db;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map.Entry;
import java.util.Set;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;
import ch.prokopovi.api.struct.BestRatePlace;
import ch.prokopovi.api.struct.BestRatesRecord;
import ch.prokopovi.db.BestRatesTable.ColumnBestRates;
import ch.prokopovi.db.RatesPlacesTable.ColumnRatesPlaces;
import ch.prokopovi.struct.Master.CurrencyCode;
import ch.prokopovi.struct.Master.OperationType;
import ch.prokopovi.struct.Master.Region;
import ch.prokopovi.struct.SimpleBestRatePlace;

public class BestRatesDbAdapter {

	private static final String LOG_TAG = "BestRatesDbAdapter";

	private static final String ALL_REGION_PLACE_RATES_QUERY = " SELECT "
			+ ColumnBestRates.VALUE
			+ "," //
			+ ColumnBestRates.CURRENCY_ID
			+ "," //
			+ ColumnRatesPlaces.REGION_ID
			+ "," //
			+ "b."
			+ ColumnRatesPlaces.RATES_PLACE_ID
			+ " "
			+ ColumnRatesPlaces.RATES_PLACE_ID
			+ "," //
			+ ColumnRatesPlaces.DESCRIPTION
			+ "," //
			+ ColumnRatesPlaces.X
			+ "," //
			+ ColumnRatesPlaces.Y
			+ "," //
			+ ColumnRatesPlaces.ADDR
			+ "," //
			+ ColumnRatesPlaces.WORK_HOURS
			+ "," //
			+ ColumnRatesPlaces.PHONE
			+ "," //
			+ ColumnBestRates.TIME_UPDATED
			+ "," //
			+ ColumnBestRates.EXCHANGE_TYPE_ID //
			+ " FROM "
			+ RatesPlacesTable.TABLE
			+ " b inner JOIN "
			+ BestRatesTable.TABLE
			+ " a ON a."
			+ ColumnBestRates.RATES_PLACE_ID
			+ " = b."
			+ ColumnRatesPlaces.RATES_PLACE_ID
			+ " WHERE "
			+ ColumnRatesPlaces.REGION_ID
			+ "=?" //
			+ " UNION " //
			+ " SELECT " //
			+ " null, " //
			+ " null, " //
			+ " p. "
			+ ColumnRatesPlaces.REGION_ID
			+ ", " //
			+ ColumnRatesPlaces.RATES_PLACE_ID
			+ ", " //
			+ ColumnRatesPlaces.DESCRIPTION
			+ ", "//
			+ ColumnRatesPlaces.X
			+ ", "//
			+ ColumnRatesPlaces.Y
			+ ", "//
			+ ColumnRatesPlaces.ADDR
			+ ", "//
			+ ColumnRatesPlaces.WORK_HOURS
			+ ", "//
			+ ColumnRatesPlaces.PHONE
			+ ", "//
			+ " null, " //
			+ " null " //
			+ " FROM "
			+ RatesPlacesTable.TABLE
			+ " p " //
			+ " WHERE "//
			+ ColumnRatesPlaces.RATES_PLACE_ID + " not in (select "
			+ ColumnBestRates.RATES_PLACE_ID + " FROM " + BestRatesTable.TABLE
			+ ") " //
			+ " AND " + ColumnRatesPlaces.REGION_ID + "=?";

	private static final String CURRENCY_EXCHANGE_TYPE_QUERY = " SELECT "
			+ ColumnBestRates.BEST_RATES_ID
			+ " _id, "
			+ ColumnBestRates.BEST_RATES_ID
			+ "," //
			+ ColumnBestRates.VALUE
			+ "," //
			+ ColumnBestRates.CURRENCY_ID
			+ "," //
			+ "b."
			+ ColumnRatesPlaces.REGION_ID
			+ "," //
			+ "b."
			+ ColumnRatesPlaces.RATES_PLACE_ID
			+ "," //
			+ ColumnRatesPlaces.DESCRIPTION
			+ "," //
			+ ColumnRatesPlaces.X
			+ "," //
			+ ColumnRatesPlaces.Y
			+ "," //
			+ ColumnRatesPlaces.ADDR
			+ "," //
			+ ColumnRatesPlaces.WORK_HOURS
			+ "," //
			+ ColumnRatesPlaces.PHONE
			+ "," //

			+ "strftime('%d-%m %H:%M', "
			+ ColumnBestRates.TIME_UPDATED
			+ "/1000, 'unixepoch', 'localtime') AS "
			+ ColumnBestRates.TIME_UPDATED
			+ "," //

			+ ColumnBestRates.EXCHANGE_TYPE_ID //

			+ " FROM " + BestRatesTable.TABLE + " a " + " LEFT JOIN "
			+ RatesPlacesTable.TABLE + " b ON a."
			+ ColumnBestRates.RATES_PLACE_ID + "=b."
			+ ColumnRatesPlaces.RATES_PLACE_ID

			+ " WHERE b." + ColumnRatesPlaces.REGION_ID + "=?" //
			+ " AND " + ColumnBestRates.EXCHANGE_TYPE_ID + "=?" //
			+ " AND " + ColumnBestRates.CURRENCY_ID + "=?" //
	;

	private static final String ORDER_LIMIT_APPENDIX_FMT = " ORDER BY "
			+ ColumnBestRates.VALUE + "	%s, " + ColumnBestRates.BEST_RATES_ID
			+ " LIMIT %d";

	private static final String LAST_UPDATE_TIME_QUERY = " SELECT MAX(a."
			+ ColumnBestRates.TIME_UPDATED
			+ ") " //
			+ " FROM  " + BestRatesTable.TABLE + " a JOIN "
			+ RatesPlacesTable.TABLE + " b ON a."
			+ ColumnBestRates.RATES_PLACE_ID + "=b."
			+ ColumnRatesPlaces.RATES_PLACE_ID //
			+ " WHERE b." + ColumnRatesPlaces.REGION_ID + " = ?";

	private static final String INSERT_RATE = "INSERT INTO "
			+ BestRatesTable.TABLE + " (" + ColumnBestRates.CURRENCY_ID + ","
			+ ColumnBestRates.EXCHANGE_TYPE_ID + ","
			+ ColumnBestRates.RATES_PLACE_ID + ","
			+ ColumnBestRates.TIME_UPDATED + "," + ColumnBestRates.VALUE
			+ ") VALUES (?,?,?,?,?);";

	private static final String DELETE_REGION_RATES = //
	" delete from "
			+ BestRatesTable.TABLE
			+ " where "
			+ ColumnBestRates.RATES_PLACE_ID //
			+ " in (select p." + ColumnRatesPlaces.RATES_PLACE_ID + " from "
			+ RatesPlacesTable.TABLE + " p where p."
			+ ColumnRatesPlaces.REGION_ID + " = ?);";

	private final SQLiteDatabase database;

	private final RatesPlacesDbAdapter placesDbAdapter;

	public BestRatesDbAdapter(SQLiteDatabase database) {
		this.database = database;
		this.placesDbAdapter = new RatesPlacesDbAdapter(database);
	}

	/**
	 * creating/updating best rates records (updating in case same-day-record
	 * found )
	 * 
	 * @param records
	 * @param bunchTimeUpdated
	 *            time in mills, equal for every rate in same group
	 */
	public void createOrUpdate(Region region,
			List<Entry<Long, BestRatesRecord>> records, Long bunchTimeUpdated) {

		if (records == null || records.size() == 0) {
			Log.w(LOG_TAG, "empty list will not update");
			return;
		}

		long start = new Date().getTime();

		final SQLiteStatement statement = this.database
				.compileStatement(INSERT_RATE);

		this.database.beginTransaction();
		try {

			this.database.execSQL(DELETE_REGION_RATES,
					new Object[] { region.getId() });

			long deletePoint = new Date().getTime();
			Log.d(LOG_TAG, "db delete old region time spent: "
					+ (deletePoint - start));

			Set<Long> placeIds = this.placesDbAdapter.existingPlaceIds();
			long placeIdsPoint = new Date().getTime();
			Log.d(LOG_TAG, "db get place ids time spent: "
					+ (placeIdsPoint - deletePoint));

			for (Entry<Long, BestRatesRecord> record : records) {

				Long placeId = record.getKey();

				boolean placeExists = placeIds.contains(placeId);
				if (!placeExists) {
					Log.w(LOG_TAG, "rate place with id [" + placeId
							+ "] does not exist. Skipped.");
					continue;
				}

				BestRatesRecord rate = record.getValue();
				int currencyId = rate.getCurrencyId();
				int exchangeTypeId = rate.getExchangeTypeId();
				Double value = rate.getValue();

				statement.clearBindings();
				statement.bindLong(1, currencyId);
				statement.bindLong(2, exchangeTypeId);
				statement.bindLong(3, placeId);
				statement.bindLong(4, bunchTimeUpdated);
				statement.bindDouble(5, value);

				statement.executeInsert();
			}

			this.database.setTransactionSuccessful();

			long spent = new Date().getTime() - placeIdsPoint;
			Log.d(LOG_TAG, "db insert time spent: " + spent);

		} finally {
			this.database.endTransaction();
		}
	}

	public Cursor fetch(Region region, OperationType operationType,
			CurrencyCode currencyCode, int limit) {

		if (region == null || operationType == null || currencyCode == null)
			throw new NullPointerException(
					"no nulls allowed in query filter params.");

		String strRegionId = String.valueOf(region.getId());
		String strOperationTypeId = String.valueOf(operationType.getId());
		String strCurrencyCodeId = String.valueOf(currencyCode.getId());
		String[] params = new String[] { strRegionId, strOperationTypeId,
				strCurrencyCodeId };

		Log.d(LOG_TAG, "params: " + Arrays.toString(params));

		String appendix = String
				.format(ORDER_LIMIT_APPENDIX_FMT, OperationType.BUY
						.equals(operationType) ? "desc" : "asc", limit);
		Cursor cursor = this.database.rawQuery(CURRENCY_EXCHANGE_TYPE_QUERY
				+ appendix, params);

		Log.d(LOG_TAG, cursor.getCount() + " records fetched");

		return cursor;
	}

	public List<BestRatePlace> fetch(Region region) {
		if (region == null)
			throw new NullPointerException(
					"no nulls allowed in query filter params.");

		String strRegionId = String.valueOf(region.getId());
		String[] params = new String[] { strRegionId, strRegionId };

		Log.d(LOG_TAG, "params: " + Arrays.toString(params));

		Cursor cursor = this.database.rawQuery(ALL_REGION_PLACE_RATES_QUERY,
				params);

		List<BestRatePlace> res = new ArrayList<BestRatePlace>();
		while (cursor.moveToNext()) {

			BestRatePlace bestRatePlace = convert(cursor);

			res.add(bestRatePlace);
		}

		cursor.close();

		Log.d(LOG_TAG, res.size() + " records fetched");

		return res;
	}

	public static BestRatePlace convert(Cursor cursor) {

		// value
		int valueIndex = cursor.getColumnIndex(ColumnBestRates.VALUE.name());
		boolean valueIsNull = cursor.isNull(valueIndex);
		Double rateValue = !valueIsNull ? cursor.getDouble(valueIndex) : null;

		// time
		int timeIndex = cursor.getColumnIndex(ColumnBestRates.TIME_UPDATED
				.name());
		boolean timeIsNull = cursor.isNull(timeIndex);
		Long rateTimeUpdated = !timeIsNull ? cursor.getLong(timeIndex) : null;

		// currency
		int currIndex = cursor.getColumnIndex(ColumnBestRates.CURRENCY_ID
				.name());
		boolean currIsNull = cursor.isNull(currIndex);
		Integer currId = !currIsNull ? cursor.getInt(currIndex) : null;

		// exch type
		int exTypeIndex = cursor
				.getColumnIndex(ColumnBestRates.EXCHANGE_TYPE_ID.name());
		boolean exTypeIsNull = cursor.isNull(exTypeIndex);
		Integer etId = !exTypeIsNull ? cursor.getInt(exTypeIndex) : null;

		// ---

		String placeDescription = cursor.getString(cursor
				.getColumnIndex(ColumnRatesPlaces.DESCRIPTION.name()));
		Double x = cursor.getDouble(cursor.getColumnIndex(ColumnRatesPlaces.X
				.name()));
		Double y = cursor.getDouble(cursor.getColumnIndex(ColumnRatesPlaces.Y
				.name()));

		String addr = cursor.getString(cursor
				.getColumnIndex(ColumnRatesPlaces.ADDR.name()));
		String workHours = cursor.getString(cursor
				.getColumnIndex(ColumnRatesPlaces.WORK_HOURS.name()));
		String phone = cursor.getString(cursor
				.getColumnIndex(ColumnRatesPlaces.PHONE.name()));

		int placeId = cursor.getInt(cursor
				.getColumnIndex(ColumnRatesPlaces.RATES_PLACE_ID.name()));

		int regionId = cursor.getInt(cursor
				.getColumnIndex(ColumnRatesPlaces.REGION_ID.name()));

		SimpleBestRatePlace ratePlace = new SimpleBestRatePlace(placeId,
				regionId, rateValue, rateTimeUpdated, placeDescription, x, y,
				addr, workHours, phone, CurrencyCode.get(currId),
				OperationType.get(etId));

		return ratePlace;
	}

	public Long fetchLastUpdateTime(Region region) {
		Long lastTime = null;

		Cursor cursor = this.database.rawQuery(LAST_UPDATE_TIME_QUERY,
				new String[] { String.valueOf(region.getId()) });

		if (cursor.moveToFirst()) {
			lastTime = cursor.getLong(0);
			Log.d(LOG_TAG, "last update for " + region + " was at " + lastTime);
		}

		cursor.close();

		return lastTime;
	}
}
